from datetime import datetime, timedelta
from enum import Enum
from logging import Logger

from pymysql.cursors import DictCursor

from util.dateutil import ts_date
from db_pool import SF_DB, DBDevice
from x_pc.core.system_context import SysDataCtx
from x_pc.db.common.service import WorkTypeService, ParameterConfigDtlService
from x_pc.exceptions import PcException
from x_pc.model.model import QueueState


class WorkCenterLocType(Enum):
    SOURCE_SINK = 1
    SOURCE = 2
    SINK = 3


class WorkCenterLoc(DBDevice):
    id: str = None
    loc: str = None
    equ_code: str = None
    # 1源目标同一工作台loc、2.源工作台loc、3.目标工作台台loc
    loc_type: int = None

    def loc_type_enum(self) -> WorkCenterLocType:
        if not self.loc:
            raise PcException(f'[PC] Center设备位置不能为空, code: {self.equ_code}, device: {self}')
        return WorkCenterLocType(self.loc_type)

    def __str__(self):
        return f'{self.__class__.__name__}(equ_code={self.equ_code}, loc={self.loc})'

    def __repr__(self):
        return self.__str__()


def get_work_center_loc() -> list[WorkCenterLoc]:
    """
    获取 工作中心 设备的 位置LOC信息
    :return:
    """
    with SF_DB().get_pool().get_connection() as conn:
        cursor = conn.cursor(DictCursor)
        data_list = cursor.db_query(
            'SELECT id, loc, wops_work_center_equequ_code AS equ_code, work_loc_type AS loc_type '
            'FROM wops_work_center_work_loc '
            'WHERE enable = 1')
        return [WorkCenterLoc(**_) for _ in data_list]


class WorkCenterEquCapacity(DBDevice):
    id: str = None
    work_type: int = None
    capacity: int = None
    unit: str = None
    equ_code: str = None

    def __str__(self):
        return f'{self.__class__.__name__}(equ_code={self.equ_code}, capacity={self.capacity}, unit={self.unit})'

    def __repr__(self):
        return self.__str__()


def get_work_center_capacity() -> list[WorkCenterEquCapacity]:
    with SF_DB().get_pool().get_connection() as conn:
        cursor = conn.cursor(DictCursor)
        data_list = cursor.db_query('SELECT id, work_type, capacity, unit, wops_work_center_equequ_code AS equ_code '
                                    'FROM wops_work_center_equ_capacity ')
        return [WorkCenterEquCapacity(**_) for _ in data_list]


# 数据库表 与 属性
TABLE_DATA_MAP = {'wops_carry_equ': 'carry_data', 'wops_transport_equ': 'transport_data',
                  'wops_work_center_equ': 'center_data', 'wops_work_unit_equ': 'unit_data',
                  'wops_equ_control_area_config': 'equ_control_area_config'}


class Sku(DBDevice):
    """
    部分自制 最终包规为 3自制箱，但无需再换包装，直接纸箱出即可
    """
    STORERKEY: str = None
    SKU: str = None
    #
    PUTAWAYCLASS: str = None

    @staticmethod
    def gen_uk(sku: str):
        return f'{sku}'

    def uk(self):
        return self.gen_uk(self.SKU)

    def __str__(self):
        return f'SKU({self.SKU})'

    def __repr__(self):
        return self.__str__()


def get_nosplit_split_sku() -> tuple[set[str], set[str]]:
    """
    获取 SKU表中 无需拆包(NOSPLIT) 与 需要拆包(SPLIT) 的SKU
    """
    with (SF_DB().get_pool().get_connection() as conn):
        cursor = conn.cursor(DictCursor)
        data_list = cursor.db_query(
            'SELECT STORERKEY, SKU, PUTAWAYCLASS FROM SKU WHERE PUTAWAYCLASS IN ("NOSPLIT", "SPLIT")')
        no_split_set = set([Sku(**_).uk() for _ in data_list if _['PUTAWAYCLASS'] == 'NOSPLIT'])
        split_set = set([Sku(**_).uk() for _ in data_list if _['PUTAWAYCLASS'] == 'SPLIT'])
        return no_split_set, split_set


def get_sys_init_data(loc_list: list[str], logger: Logger) -> SysDataCtx:
    """
    获取系统初始化数据
    :param loc_list: loc列表
    :param logger:
    :return:
    """
    sys_data_ctx = SysDataCtx()
    sys_data_ctx.logger = logger
    sfdb = SF_DB()
    #
    pool = sfdb.get_pool()
    for table in TABLE_DATA_MAP:
        with pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            db_data = cursor.db_query('SELECT * FROM {}'.format(table))
            setattr(sys_data_ctx, TABLE_DATA_MAP[table], db_data)
    sys_data_ctx.get_zb_lane_data()
    # 工作中心 位置信息
    sys_data_ctx.center_loc_data = get_work_center_loc()
    # 工作中心 工作能力(耗时信息)
    sys_data_ctx.center_capacity_data = get_work_center_capacity()
    # 工作类型 WorkType
    sys_data_ctx.work_type_list = WorkTypeService().get_data()
    # 缓存 货格 位置耗时数据
    locd_list = get_locs_distances(loc_list)
    locd_dict = {}
    for locd in locd_list:
        locd_dict[locd.loc] = locd
    sys_data_ctx.lane_loc_distance_dict = locd_dict
    # 设备运行时长系数
    sys_data_ctx.time_cost_ratio = 1
    try:
        time_dtl = ParameterConfigDtlService.get_group_code_data('schedule_config', 'key_redfactor')
        if time_dtl and time_dtl.value:
            sys_data_ctx.time_cost_ratio = float(time_dtl.value)
    except Exception as ex:
        warning_msg = f'[PC] 设备运行时长 系数设置异常，默认置为: {sys_data_ctx.time_cost_ratio} -> {ex}'
        sys_data_ctx.logger.warning(warning_msg)
    # 模拟器运行时长限制
    sys_data_ctx.sim_time_limit = 4 * 60 * 60
    try:
        # 单位 小时H
        sim_time_hours = ParameterConfigDtlService.get_group_code_data('schedule_config', 'pc_sim_time')
        if sim_time_hours and sim_time_hours.value:
            sys_data_ctx.sim_time_limit = int(sim_time_hours.value) * 60 * 60
    except Exception as ex:
        warning_msg = f'[PC] 模拟器时长运行限制 系数设置异常，默认置为: {sys_data_ctx.sim_time_limit / 60 / 60} Hours -> {ex}'
        sys_data_ctx.logger.warning(warning_msg)
    # 作业发车时间 排程提前量
    sys_data_ctx.advance_vehicle_time = 0
    try:
        # 单位 分钟MIN
        advance_time_minutes = ParameterConfigDtlService.get_group_code_data('schedule_config',
                                                                             'pc_advance_vehicle_time')
        if advance_time_minutes and advance_time_minutes.value:
            sys_data_ctx.advance_vehicle_time = int(advance_time_minutes.value) * 60
    except Exception as ex:
        warning_msg = f'[PC] 作业发车时间排程提前量 系数设置异常，默认置为: {sys_data_ctx.advance_vehicle_time / 60} Minutes -> {ex}'
        sys_data_ctx.logger.warning(warning_msg)
    # 是否指定出货口
    sys_data_ctx.is_designate_outlet = False
    try:
        val = ParameterConfigDtlService.get_group_code_data('schedule_config', 'is_designate_outlet')
        if val and val.value in (1, '1'):
            sys_data_ctx.is_designate_outlet = True
        sys_data_ctx.logger.info('[PC] 是否指定出货口, 设置为: %s', sys_data_ctx.is_designate_outlet)
    except Exception as ex:
        warning_msg = f'[PC] 是否指定出货口 系数设置异常，默认置为: {sys_data_ctx.is_designate_outlet} -> {ex}'
        sys_data_ctx.logger.warning(warning_msg)
    # 需要隔离的 在库任务
    try:
        sys_data_ctx.inv_isolated_task = []
        #
        command_str = ParameterConfigDtlService.get_group_code_data('schedule_config', 'inv_isolated_task')
        command_list = command_str.value.split(',')
        if command_list:
            sys_data_ctx.inv_isolated_task = [_.strip() for _ in command_list if _]
    except Exception as ex:
        warning_msg = f'[PC] 在库需按单隔离出口的作业 设置异常，默认置为: {sys_data_ctx.inv_isolated_task} -> {ex}'
        sys_data_ctx.logger.warning(warning_msg)

    # -1自制无需 换自制箱任务
    sys_data_ctx.nosplit_skus, sys_data_ctx.split_skus = get_nosplit_split_sku()

    # 设置任务出货口延续的最长任务开始时间
    sys_data_ctx.port_continued_time = 8
    try:
        port_time = ParameterConfigDtlService.get_group_code_data('schedule_config', 'pc_port_time')
        if port_time and port_time.value:
            sys_data_ctx.port_continued_time = float(port_time.value)
    except Exception as ex:
        warning_msg = f'[PC] 任务出货口延续的最长时间 系数设置异常，默认置为: {sys_data_ctx.port_continued_time} -> {ex}'
        sys_data_ctx.logger.warning(warning_msg)
    return sys_data_ctx


class LocDistance(object):
    """
    货位(栈板、件箱) 位置信息
    """
    loc: str = None
    time: float = None
    move_time: float = None
    lift_time: float = None

    def __init__(self, **kwargs):
        for key in kwargs:
            if hasattr(self, key):
                setattr(self, key, kwargs[key])

    def __str__(self):
        return f'{self.__class__.__name__}(time={self.time})'


def get_loc_distance(loc: str) -> LocDistance | None:
    """
    获取货物 位置 耗时
    :return:
    """
    if not loc:
        return None
    with SF_DB().get_pool().get_connection() as conn:
        cursor = conn.cursor(DictCursor)
        db_data = cursor.db_query('SELECT loc, time, move_time, lift_time '
                                  'FROM wops_loc_distance '
                                  'WHERE loc = "{}" LIMIT 1'.format(loc))
        if db_data:
            db_data = db_data[0]
    if db_data:
        return LocDistance(**db_data)
    return None


def get_locs_distances(locs: list[str]) -> list[LocDistance]:
    if not locs:
        return []
    with SF_DB().get_pool().get_connection() as conn:
        cursor = conn.cursor(DictCursor)
        data_list = cursor.db_query('SELECT loc, time, move_time, lift_time '
                                    'FROM wops_loc_distance '
                                    'WHERE loc in %s', (locs,))
        return [LocDistance(**_) for _ in data_list]


def get_prev_vehicle_port(equ_types: set[int],port_continued_time: float,base_time: float) -> dict[int, dict[str, list[str]]]:
    """
    获取指定波次的 出货口 交付对象 的具体出货口占用
    需获取 [已下发3/11 已完成4] 状态的数据即可
    理论上 存在历史完成且匹配的任务，出货口不会有复用的情况出现

    :param equ_types: 设备类型(出货口类别Code)
    """
    if not equ_types:
        return {}
    port = dict(zip(equ_types, [{} for _ in range(len(equ_types))]))
    check_time = datetime.fromtimestamp(base_time-port_continued_time *60 *60).strftime('%Y-%m-%d %H:%M:%S') if base_time else None
    with SF_DB().get_pool().get_connection() as conn:
        cursor = conn.cursor(DictCursor)
        data_list = cursor.db_query(
            'SELECT q.wops_vehicle_taskid, p.target_work_loc, p.equ_type, q.deliver_factory_code, q.state, p.plan_end '
            'FROM wops_kwms_work_queue q '
            'LEFT JOIN wops_kwms_queue_pool_ralation r ON r.wops_kwms_work_queue_task_id = q.task_id '
            'LEFT JOIN wops_kwms_work_pool p ON p.sub_task_id = r.wops_kwms_work_pool_sub_task_id '
            'WHERE q.state IN %s AND p.equ_type IN %s AND q.add_date > %s',
            (
                (QueueState.ISSUE.value, QueueState.ISSUE2.value, QueueState.ASSIGNING.value),
                [str(_) for _ in equ_types],
                check_time
            )
        )
        # 按交付对象 进行处理
        for data in data_list:
            equ_type = data['equ_type']
            factory = data['deliver_factory_code']
            target_loc = data['target_work_loc']
            if factory not in port[equ_type]:
                port[equ_type][factory] = []
            port[equ_type][factory].append(target_loc)
            port[equ_type][factory] = list(set(port[equ_type][factory]))
    return port


def get_zk_prev_vehicle_port(equ_types: set[int]) -> dict[int, dict[str, list[str]]]:
    """
    获取在库 历史出货口分配数据
    """
    if not equ_types:
        return {}
    port = dict(zip(equ_types, [{} for _ in range(len(equ_types))]))
    with SF_DB().get_pool().get_connection() as conn:
        cursor = conn.cursor(DictCursor)
        data_list = cursor.db_query(
            'SELECT q.wops_vehicle_taskid, p.target_work_loc, p.equ_type, q.orderdetil, q.state, p.plan_end '
            'FROM wops_kwms_work_queue q '
            'LEFT JOIN wops_kwms_queue_pool_ralation r ON r.wops_kwms_work_queue_task_id = q.task_id '
            'LEFT JOIN wops_kwms_work_pool p ON p.sub_task_id = r.wops_kwms_work_pool_sub_task_id '
            'WHERE q.state IN %s AND p.equ_type IN %s',
            (
                (QueueState.ISSUE.value, QueueState.ISSUE2.value, QueueState.ASSIGNING.value),
                [str(_) for _ in equ_types]
            )
        )
        # 按订单号 进行处理
        for data in data_list:
            equ_type = data['equ_type']
            factory = data['orderdetil']
            target_loc = data['target_work_loc']
            if factory not in port[equ_type]:
                port[equ_type][factory] = []
            port[equ_type][factory].append(target_loc)
            port[equ_type][factory] = list(set(port[equ_type][factory]))
    return port


def get_statistical_factory_port(equ_types: set[int],port_continued_time: float,base_time: float) -> dict[int, dict[str, int]]:
    """
    获取 205/206 出货口，历史出货量

    :param equ_types: 设备类型(出货口类别Code)
    """
    if not equ_types:
        return {}
    port = dict(zip(equ_types, [{} for _ in range(len(equ_types))]))
    check_time = datetime.fromtimestamp(base_time - port_continued_time * 60 * 60).strftime(
        '%Y-%m-%d %H:%M:%S') if base_time else None
    port = dict(zip(equ_types, [{} for _ in range(len(equ_types))]))
    with SF_DB().get_pool().get_connection() as conn:
        cursor = conn.cursor(DictCursor)
        data_list = cursor.db_query(
            'SELECT q.wops_vehicle_taskid, p.target_work_loc, p.equ_type, q.deliver_factory_code, q.state, p.plan_end '
            'FROM wops_kwms_work_queue q '
            'LEFT JOIN wops_kwms_queue_pool_ralation r ON r.wops_kwms_work_queue_task_id = q.task_id '
            'LEFT JOIN wops_kwms_work_pool p ON p.sub_task_id = r.wops_kwms_work_pool_sub_task_id '
            'WHERE q.state IN %s AND p.equ_type IN %s AND q.add_date > %s',
            (
                (QueueState.ISSUE.value, QueueState.ISSUE2.value, QueueState.ASSIGNING.value),
                [str(_) for _ in equ_types],
                check_time
            )
        )
        for data in data_list:
            equ_type = data['equ_type']
            target_loc = data['target_work_loc']
            if target_loc not in port[equ_type]:
                port[equ_type][target_loc] = 0
            port[equ_type][target_loc] += 1
    #
    return port


class WorkCalendar(DBDevice):
    work_date: str = None
    begin_time: datetime = None
    end_time: datetime = None

    def work_ts(self) -> tuple[float, float]:
        return self.begin_time.timestamp(), self.end_time.timestamp()


class WorkCalendarService(object):
    """
    工作日历
    """

    @staticmethod
    def get_work_calendar(base_time: float) -> list[WorkCalendar]:
        base_datetime = ts_date(base_time) if base_time else datetime.now()
        with SF_DB().get_pool().get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query(
                'SELECT work_date, begin_time, end_time '
                'FROM wops_work_calendar '
                'WHERE work_date IN (%s, ADDDATE(%s, INTERVAL 1 DAY)) AND type = 1 '
                'ORDER BY seq', (base_datetime.date(), base_datetime.date()))
            return [WorkCalendar(**_) for _ in data_list]


class OrderDetail(DBDevice):
    SERIALKEY: str = None
    ADDDATE: datetime = None


class OrderDetailService(object):
    """
    订单详情
    """

    @staticmethod
    def order_add_date(task_ids: list[str]) -> list[OrderDetail]:
        with SF_DB().get_pool().get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query(
                'SELECT SERIALKEY, ADDDATE FROM orderdetail WHERE SERIALKEY IN (%s)',
                (','.join(task_ids))
            )
            return [OrderDetail(**_) for _ in data_list]


if __name__ == '__main__':
    loc = get_loc_distance('1031062002000')
    print(loc)
    #
    center_loc = get_work_center_loc()
    print(center_loc)
    #
    loc_list = get_locs_distances(['1031062002000', '1031062003000'])
    print(loc_list)
    #
    vehicle_dict = get_prev_vehicle_port()
    print(vehicle_dict)
    #
    nosplit_sku, split_sku = get_nosplit_split_sku()
    print(nosplit_sku)
    print(split_sku)
